﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using Pap.Data.Abstraction.Utils;
using Utils;

namespace Pap.Data.LToS.Repositories
{
    /// <summary>
    /// 
    /// </summary>
    public class ReportRepository : IReportRepository
    {
        /// <summary>
        /// 
        /// </summary>
        IDatabase _Database;
        /// <summary>
        /// 
        /// </summary>
        IWorkFlow _WorkFlow;

        /// <summary>
        /// Initializes a new instance of the <see cref="ReportRepository"/> class.
        /// </summary>
        /// <param name="db">The db.</param>
        /// <param name="wf">The wf.</param>
        public ReportRepository(IDatabase db, IWorkFlow wf)
        {
            _Database = db;
            _WorkFlow = wf;
        }

        #region IReportRepository Members

        /// <summary>
        /// Finds the users.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IUser> FindUsers(System.Linq.Expressions.Expression<Func<IUser, bool>> where)
        {
            return _Database.Users.Where(where);
        }

        /// <summary>
        /// Finds the projects.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IProject> FindProjects(System.Linq.Expressions.Expression<Func<IProject, bool>> where)
        {
            return _Database.Projects.Where(where);
        }

        /// <summary>
        /// Gets the monthly responsible report.
        /// </summary>
        /// <param name="userID">The user ID.</param>
        /// <param name="projectID">The project ID.</param>
        /// <param name="dateTime">The date time.</param>
        /// <returns></returns>
        public IEnumerable GetMonthlyResponsibleReport(Guid userID, Guid projectID, DateTime dateTime)
        {
            Guid periodicalPMethod = Enums.ToString(PaymentMethods.Periodical).ToGuid();
            var result = from customer in _Database.Customers
                         join customerLoan in _Database.CustomerLoans on customer.CustomerID equals customerLoan.CustomerID
                         join boardLoan in _Database.BoardLoans on customerLoan.LoanID equals boardLoan.LoanID
                         join board in _Database.Boards on boardLoan.BoardID equals board.BoardID
                         join loanType in _Database.LoanTypes on customerLoan.LoanTypeID equals loanType.ID
                         join projectStage in _Database.ProjectStages on customerLoan.ProjectStageID equals projectStage.ProjectStageID
                         join loanExecution in _Database.LoanExecutions on customerLoan.LoanID equals loanExecution.LoanID
                         join loanPMethod in _Database.LoanPaymentMethod on loanExecution.LoanPaymentMethodID equals loanPMethod.LoanPaymentMethodID
                         join user in _Database.Users on customerLoan.ResponsibleID equals user.UserID

                         where user.UserID == userID && projectStage.ProjectID == projectID && loanExecution.ExecutionDate > dateTime

                         orderby customerLoan.LoanNumber

                         select new
                         {
                             customerLoan.LoanNumber,
                             customer.CustomerName,
                             customer.Gender,
                             LoanType = loanType.Name,
                             ProjectStatus = customerLoan.IsLoanProjectExist,
                             projectStage.Stage,
                             loanExecution.LoanAmount,
                             loanExecution.LoanPeriod,
                             InterestAmount = loanExecution.TotalLoanAmount - loanExecution.LoanAmount,
                             loanExecution.TotalLoanAmount,
                             GracePeriod = (loanExecution.LoanPaymentMethodID == periodicalPMethod ? null : (loanExecution.LoanPeriod - 1).ToString()),
                             loanExecution.CheckNumber,
                             CheckDate = loanExecution.CheckDate.Value,
                             ExecutionDate = loanExecution.ExecutionDate
                         };
            return result;
        }

        /// <summary>
        /// Gets the male percent.
        /// </summary>
        /// <param name="projectID">The project ID.</param>
        /// <param name="dateTime">The date time.</param>
        /// <returns></returns>
        public double GetMalePercent(Guid projectID, DateTime dateTime)
        {
            var result = from customer in _Database.Customers
                         join customerLoan in _Database.CustomerLoans on customer.CustomerID equals customerLoan.CustomerID
                         join projectStage in _Database.ProjectStages on customerLoan.ProjectStageID equals projectStage.ProjectStageID
                         join loanExecution in _Database.LoanExecutions on customerLoan.LoanID equals loanExecution.LoanID

                         where projectStage.ProjectID == projectID && loanExecution.ExecutionDate > dateTime
                         select customer.Gender;

            double total = result.Count();
            return result.Where(g => g == CustomerGender.Male).Count() / total;

        }

        /// <summary>
        /// Gets the exisiting loans percent.
        /// </summary>
        /// <param name="projectID">The project ID.</param>
        /// <param name="dateTime">The date time.</param>
        /// <returns></returns>
        public double GetExisitingLoansPercent(Guid projectID, DateTime dateTime)
        {
            var result = from customer in _Database.Customers
                         join customerLoan in _Database.CustomerLoans on customer.CustomerID equals customerLoan.CustomerID
                         join projectStage in _Database.ProjectStages on customerLoan.ProjectStageID equals projectStage.ProjectStageID
                         join loanExecution in _Database.LoanExecutions on customerLoan.LoanID equals loanExecution.LoanID

                         where projectStage.ProjectID == projectID && loanExecution.ExecutionDate > dateTime
                         select customerLoan.IsLoanProjectExist;

            double total = result.Count();
            return result.Where(b => b).Count() / total;

        }

        /// <summary>
        /// Gets the male activity.
        /// </summary>
        /// <param name="projectID">The project ID.</param>
        /// <param name="dateTime">The date time.</param>
        /// <returns></returns>
        public IEnumerable GetMaleActivity(Guid projectID, DateTime dateTime)
        {
            return (
                from loanType in _Database.LoanTypes
                from g in (_Database.Customers.Select(c => c.Gender).Distinct())
                select new
                {
                    TypeName = loanType.Name,
                    Gender = g,
                    Count = (
                        from cl in _Database.CustomerLoans
                        join cust in _Database.Customers on cl.CustomerID equals cust.CustomerID
                        join lt in _Database.LoanTypes on cl.LoanTypeID equals lt.ID
                        join le in _Database.LoanExecutions on cl.LoanID equals le.LoanID
                        join ps in _Database.ProjectStages on cl.ProjectStageID equals ps.ProjectStageID

                        where lt.ID == loanType.ID && cust.Gender == g && le.ExecutionDate > dateTime && ps.ProjectID == projectID

                        select cl.LoanID
                    ).Count()
                });
        }

        /// <summary>
        /// Gets the activity percents.
        /// </summary>
        /// <param name="projectID">The project ID.</param>
        /// <param name="dateTime">The date time.</param>
        /// <returns></returns>
        public IEnumerable GetActivityPercents(Guid projectID, DateTime dateTime)
        {
            return from customer in _Database.Customers
                   join customerLoan in _Database.CustomerLoans on customer.CustomerID equals customerLoan.CustomerID
                   join loanType in _Database.LoanTypes on customerLoan.LoanTypeID equals loanType.ID
                   join projectStage in _Database.ProjectStages on customerLoan.ProjectStageID equals projectStage.ProjectStageID
                   join loanExecution in _Database.LoanExecutions on customerLoan.LoanID equals loanExecution.LoanID

                   where projectStage.ProjectID == projectID && loanExecution.ExecutionDate > dateTime

                   group loanType by loanType into g
                   select new
                   {
                       TypeName = g.Key.Name,
                       Count = g.Count()
                   };
        }

        /// <summary>
        /// Gets the loan count.
        /// </summary>
        /// <param name="projectID">The project ID.</param>
        /// <param name="dateTime">The date time.</param>
        /// <returns></returns>
        public int GetLoanCount(Guid projectID, DateTime dateTime)
        {
            return (from customer in _Database.Customers
                    join customerLoan in _Database.CustomerLoans on customer.CustomerID equals customerLoan.CustomerID
                    join loanType in _Database.LoanTypes on customerLoan.LoanTypeID equals loanType.ID
                    join projectStage in _Database.ProjectStages on customerLoan.ProjectStageID equals projectStage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customerLoan.LoanID equals loanExecution.LoanID

                    where projectStage.ProjectID == projectID && loanExecution.ExecutionDate > dateTime

                    select customerLoan.LoanTypeID).Count();
        }

        /// <summary>
        /// Systems the overall.
        /// </summary>
        /// <param name="sysParam">The sys param.</param>
        /// <returns></returns>
        public IEnumerable SystemOverall(SystemOverallParameters sysParam)
        {

            return from customersloans in _Database.CustomerLoans
                   join customers in _Database.Customers on customersloans.CustomerID equals customers.CustomerID
                   join loansocialresearch in _Database.LoanSocialResearches on customersloans.LoanID equals loansocialresearch.LoanID
                   join loanexecution in _Database.LoanExecutions on customersloans.LoanID equals loanexecution.LoanID
                   join loanpaymentmethod in _Database.LoanPaymentMethod on loanexecution.LoanPaymentMethodID equals loanpaymentmethod.LoanPaymentMethodID
                   join boardloans in _Database.BoardLoans on customersloans.LoanID equals boardloans.LoanID
                   join board in _Database.Boards on boardloans.BoardID equals board.BoardID
                   join projectstage in _Database.ProjectStages on customersloans.ProjectStageID equals projectstage.ProjectStageID
                   join project in _Database.Projects on projectstage.ProjectID equals project.ProjectID

                   where (customersloans.IsLoanProjectExist == sysParam.isloanprojectexist)
                   where (customers.Gender == sysParam.customergender)
                   where (customers.EducationalLevelID == sysParam.educationallevel)
                   where (customersloans.ProjectStageID == sysParam.projectstage)
                   where (sysParam.project.Contains(customersloans.ProjectStage.ProjectID))
                   where (customers.SocialStatusID == sysParam.socialstatus)
                   where (customersloans.LoanTypeID == sysParam.loantype)
                   where (customersloans.LoanPlaceTypeID == sysParam.loanplacetype)
                   where (sysParam.committes.Contains(customersloans.CommitteeID))
                   where (customersloans.ResponsibleID == sysParam.responsible)

                   where (loansocialresearch.FamilyNumbers >= sysParam.familynumberfrom && loansocialresearch.FamilyNumbers <= sysParam.familynumberto)
                   where (loansocialresearch.IncomeAmount >= sysParam.averageincomefrom && loansocialresearch.IncomeAmount <= sysParam.averageincometo)
                   where (loanexecution.LoanPeriod == sysParam.loanpaymentperiod)
                   where (loanexecution.LoanAmount == sysParam.loanexecutioamount)
                   where (board.BoardNumber == sysParam.boardnumber)

                   where (loanexecution.ExecutionDate >= sysParam.loanexecutiondatefrom && loanexecution.ExecutionDate <= sysParam.loanexecutiondateto)
                   where (customersloans.LoanCreatedDate >= sysParam.requestdatefrom && customersloans.LoanCreatedDate <= sysParam.requestdateto)
                   where (board.BoardDate >= sysParam.boarddatefrom && board.BoardDate <= sysParam.boarddateto)
                   select new
                   {
                       LoanNumber = customersloans.LoanNumber,
                       CustomerName = customers.CustomerName,
                       SSNNumber = customers.SSN,
                       LoanProject = project.ProjectName,
                       LoanAmount = loanexecution.LoanAmount,
                       LoanPeriod = loanexecution.LoanPeriod,
                       LoanActualAmount = (from loaninstallmentpayment in _Database.LoanInstallmentPayments
                                           join loanexcecutioninstallment in _Database.LoanExecutionInstallments on loaninstallmentpayment.LoanExecutionInstallmentID equals loanexcecutioninstallment.LoanExecutionInstallmentID
                                           where (loanexcecutioninstallment.LoanExecutionID == loanexecution.LoanExecutionID)
                                           group loaninstallmentpayment by loaninstallmentpayment.PaymentAmount),
                       LoanReturned = (from loanexcecutioninstallment in _Database.LoanExecutionInstallments
                                       where (loanexcecutioninstallment.LoanExecutionID == loanexecution.LoanExecutionID)
                                       where (loanexcecutioninstallment.LoanInstallmentPaymentLst.Count() == 0)
                                       group loanexcecutioninstallment by loanexcecutioninstallment.TotalInstallmentAmount),
                       LoanPaymentMethod = loanpaymentmethod.LoanPaymentMethodName,
                   };




        }

        /// <summary>
        /// Gets the waiting list.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="Committe">The committe.</param>
        /// <returns></returns>
        public IEnumerable GetWaitingList(List<string> Projects, List<string> Committe)
        {
            return
                from x in
                    (
                        from customersLoans in _Database.CustomerLoans
                        join wfprocessInstances in _WorkFlow.wfProcessInstances on customersLoans.LoanID equals wfprocessInstances.EntityID
                        join wfstepInstances in _WorkFlow.wfStepInstances on wfprocessInstances.ProcessInstanceID equals wfstepInstances.ProcessInstanceID
                        join wfSteps in _WorkFlow.wfSteps on wfstepInstances.StepID equals wfSteps.StepID
                        where (Projects.Count > 0 ? Projects.Contains(customersLoans.ProjectStage.ProjectID.ToString()) : true)
                        where (Committe.Count > 0 ? Committe.Contains(customersLoans.CommitteeID.ToString()) : true)

                        select new
                        {
                            StepName = wfSteps.DisplayName,
                            CustomersLoanAmount = customersLoans.LoanAmount,
                        })
                group x by new { x.StepName, x.CustomersLoanAmount } into g
                select new
                {
                    StepName = g.Key.StepName,
                    StepCount = g.Count(),
                    LoanAmount = g.Key.CustomersLoanAmount,
                };
        }

        /// <summary>
        /// Finds the committee.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICommittee> FindCommittee(System.Linq.Expressions.Expression<Func<ICommittee, bool>> where)
        {
            return _Database.Committees.Where(where);
        }

        /// <summary>
        /// Controlthes the movementof usersofthe system.
        /// </summary>
        /// <param name="Committee">The committee.</param>
        /// <param name="UserName">Name of the user.</param>
        /// <param name="LoanNumber">The loan number.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public IEnumerable ControltheMovementofUsersoftheSystem(Guid Committee, Guid? UserName, int? LoanNumber, DateTime FromDate, DateTime ToDate)
        {
            return from customersLoans in _Database.CustomerLoans
                   join wfprocessInstances in _WorkFlow.wfProcessInstances on customersLoans.LoanID equals wfprocessInstances.EntityID
                   join wfstepInstances in _WorkFlow.wfStepInstances on wfprocessInstances.ProcessInstanceID equals wfstepInstances.ProcessInstanceID
                   join wfSteps in _WorkFlow.wfSteps on wfstepInstances.StepID equals wfSteps.StepID
                   join users in _Database.Users on customersLoans.ResponsibleID equals users.UserID
                   join customers in _Database.Customers on customersLoans.CustomerID equals customers.CustomerID
                   where (LoanNumber != null ? customersLoans.LoanNumber == LoanNumber : true)
                   where (UserName.Value.ToString() != "00000000-0000-0000-0000-000000000000" ? users.UserID.Equals(UserName) : true)
                   where (customersLoans.CommitteeID.Equals(Committee))
                   where (wfstepInstances.CreationDate >= FromDate && wfstepInstances.CreationDate <= ToDate)
                   orderby wfstepInstances.CreationDate
                   select new
                   {
                       DisplayName = users.Username,
                       StepDisplayName = wfSteps.DisplayName,
                       LoanNumber = customersLoans.LoanNumber,
                       CustomerName = customers.CustomerName,
                       ActionDisplayName = wfstepInstances.wfActionType.DisplayName,
                       CreationDate = wfstepInstances.CreationDate,
                       CommitteeName = customersLoans.CommitteeObj.CommitteeName
                   };

        }

        /// <summary>
        /// Finds the project stage.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IProjectStage> FindProjectStage(System.Linq.Expressions.Expression<Func<IProjectStage, bool>> where)
        {
            return _Database.ProjectStages.Where(where);
        }

        /// <summary>
        /// Finds the social statuses.
        /// </summary>
        /// <param name="Where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ILookUpEntity> FindSocialStatuses(System.Linq.Expressions.Expression<Func<ILookUpEntity, bool>> Where)
        {
            return this._Database.SocialStatuses.OfType<ILookUpEntity>();
        }

        /// <summary>
        /// Finds the educational levels.
        /// </summary>
        /// <param name="Where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ILookUpEntity> FindEducationalLevels(System.Linq.Expressions.Expression<Func<ILookUpEntity, bool>> Where)
        {
            return this._Database.EducationalLevels.OfType<ILookUpEntity>();
        }

        /// <summary>
        /// Finds the loan types.
        /// </summary>
        /// <param name="Where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ILookUpEntity> FindLoanTypes(System.Linq.Expressions.Expression<Func<ILookUpEntity, bool>> Where)
        {
            return this._Database.LoanTypes.Where(Where);
        }

        /// <summary>
        /// Finds the loan places.
        /// </summary>
        /// <param name="Where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ILookUpEntity> FindLoanPlaces(System.Linq.Expressions.Expression<Func<ILookUpEntity, bool>> Where)
        {
            return this._Database.LoanPlaceTypes.Where(Where);
        }

        /// <summary>
        /// Finds the users transactions.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IVUsersTransaction> FindUsersTransactions(System.Linq.Expressions.Expression<Func<IVUsersTransaction, bool>> where)
        {
            return _Database.VUsersTransactions.Where(where);
        }

        /// <summary>
        /// Finds the monthly responsible report.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IVMonthlyResponsibleReport> FindMonthlyResponsibleReport(System.Linq.Expressions.Expression<Func<IVMonthlyResponsibleReport, bool>> where)
        {
            return _Database.VMonthlyResponsibleReport.Where(where);
        }

        /// <summary>
        /// Finds the monthly accountant data.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IVMonthlyAccountantReport> FindMonthlyAccountantData(System.Linq.Expressions.Expression<Func<IVMonthlyAccountantReport, bool>> where)
        {
            return _Database.VMonthlyAccountantReport.Where(where);
        }

        /// <summary>
        /// Finds the waiting list report.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IVWaitingListReport> FindWaitingListReport(System.Linq.Expressions.Expression<Func<IVWaitingListReport, bool>> where)
        {
            return _Database.VWaitingListReport.Where(where);
        }

        #region UnionStatusReport

        /// <summary>
        /// Gets the union status count of requests.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfRequests(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? customersLoans.LoanCreatedDate >= FromDate : true)
                    where (ToDate.HasValue ? customersLoans.LoanCreatedDate <= ToDate : true)
                    select customersLoans.LoanID).Count();





            //TotalAmountTransferFromProject 

            //TheAmountOfSpentInThisPeriod 
            //TotalOfRepaymentWithoutFines 
            //TheDueAmountinThisPeriod 
            //TheFundingFromPAP 
            //TotalFinesCollected 
            //TheAmountOfOpenedCustomersLoans 
            //TheNumberOfLoansClosed 
            //TheBenefitsRepayed 
            //TheRenewedLoans
            //RepaymentPercentageInThisPeriod 
            //TheNumberOftheStatmentsRepeatedinThisPeriod 
            //TheNumberOfPrimaryLoans 
            //ThenumberOfSecondaryLoans 
            //TheNumberOfLoansThatExceedTheThreeStage 
            //TheNumberOfLoansTransferedToCourt 
            //TheAmountOfBalanceOfCustomers 

        }

        /// <summary>
        /// Gets the union status count of accepted loan.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfAcceptedLoan(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from loanexecution in _Database.LoanExecutions
                    join customersLoans in _Database.CustomerLoans on loanexecution.LoanID equals customersLoans.LoanID
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? customersLoans.LoanCreatedDate >= FromDate : true)
                    where (ToDate.HasValue ? customersLoans.LoanCreatedDate <= ToDate : true)
                    select loanexecution.LoanID).Count();
        }

        /// <summary>
        /// Gets the union status count ofrefused loan.
        /// </summary>
        /// <param name="projects">The projects.</param>
        /// <param name="fromDate">From date.</param>
        /// <param name="toDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfrefusedLoan(List<Guid> projects, DateTime? fromDate, DateTime? toDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join workflowprocessinstances in _WorkFlow.wfProcessInstances on customersLoans.LoanID equals workflowprocessinstances.EntityID
                    join workflowinstancessteps in _WorkFlow.wfStepInstances on workflowprocessinstances.ProcessInstanceID equals workflowinstancessteps.ProcessInstanceID
                    join workflowsteps in _WorkFlow.wfSteps on workflowinstancessteps.StepID equals workflowsteps.StepID
                    where (workflowsteps.StepID.Equals(Enums.ToString(Pap.Services.WorkflowEngine.Steps.RefusedLoans).ToGuid()))
                    where (projects.Count() != 0 ? projects.Contains(projectstage.ProjectID) : true)
                    where (fromDate.HasValue ? customersLoans.LoanCreatedDate >= fromDate : true)
                    where (toDate.HasValue ? customersLoans.LoanCreatedDate <= toDate : true)
                    select customersLoans.LoanID).Count();
        }

        /// <summary>
        /// Gets the union status count of loan executed.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfLoanExecuted(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                    //join workflowprocessinstances in _WorkFlow.wfProcessInstances on customersLoans.LoanID equals workflowprocessinstances.EntityID
                    //join workflowinstancessteps in _WorkFlow.wfStepInstances on workflowprocessinstances.ProcessInstanceID equals workflowinstancessteps.ProcessInstanceID
                    //join workflowsteps in _WorkFlow.wfSteps on workflowinstancessteps.StepID equals workflowsteps.StepID
                    //where (workflowsteps.StepID.Equals("B6B79EBB-AF73-8DB5-1603-C2F66A8CD1AF"))
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? customersLoans.LoanCreatedDate >= FromDate : true)
                    where (ToDate.HasValue ? customersLoans.LoanCreatedDate <= ToDate : true)
                    select customersLoans.LoanID).Count();
        }

        /// <summary>
        /// Gets the union status count of loan stopped from execution.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfLoanStoppedFromExecution(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join workflowprocessinstances in _WorkFlow.wfProcessInstances on customersLoans.LoanID equals workflowprocessinstances.EntityID
                    join workflowinstancessteps in _WorkFlow.wfStepInstances on workflowprocessinstances.ProcessInstanceID equals workflowinstancessteps.ProcessInstanceID
                    join workflowsteps in _WorkFlow.wfSteps on workflowinstancessteps.StepID equals workflowsteps.StepID
                    where (workflowsteps.StepID.Equals("411ADCF8-19AF-8C41-C71C-713B3DEAA2CB"))
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? customersLoans.LoanCreatedDate >= FromDate : true)
                    where (ToDate.HasValue ? customersLoans.LoanCreatedDate <= ToDate : true)
                    select customersLoans.LoanID).Count();
        }

        /// <summary>
        /// Gets the union status total amount of executed loans.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public double GetUnionStatusTotalAmountOfExecutedLoans(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                    //join workflowprocessinstances in _WorkFlow.wfProcessInstances on customersLoans.LoanID equals workflowprocessinstances.EntityID
                    //join workflowinstancessteps in _WorkFlow.wfStepInstances on workflowprocessinstances.ProcessInstanceID equals workflowinstancessteps.ProcessInstanceID
                    //join workflowsteps in _WorkFlow.wfSteps on workflowinstancessteps.StepID equals workflowsteps.StepID
                    //where (workflowsteps.StepID.Equals("B6B79EBB-AF73-8DB5-1603-C2F66A8CD1AF"))
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? customersLoans.LoanCreatedDate >= FromDate : true)
                    where (ToDate.HasValue ? customersLoans.LoanCreatedDate <= ToDate : true)
                    select (double?)customersLoans.LoanAmount).Sum() ?? 0;
        }

        /// <summary>
        /// Gets the union status total amount of spent.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public double GetUnionStatusTotalAmountOfSpent(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from loanexecution in _Database.LoanExecutions
                    join customersLoans in _Database.CustomerLoans on loanexecution.LoanID equals customersLoans.LoanID
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? loanexecution.ExecutionDate >= FromDate : true)
                    where (ToDate.HasValue ? loanexecution.ExecutionDate <= ToDate : true)
                    select (double?)loanexecution.LoanAmount).Sum() ?? 0;
        }

        /// <summary>
        /// Gets the union status total of repayment without fines.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public double GetUnionStatusTotalOfRepaymentWithoutFines(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                    join loanExecutionInstallments in _Database.LoanExecutionInstallments on loanExecution.LoanExecutionID equals loanExecutionInstallments.LoanExecutionID
                    join loanInstallmentPayments in _Database.LoanInstallmentPayments on loanExecutionInstallments.LoanExecutionInstallmentID equals loanInstallmentPayments.LoanExecutionInstallmentID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? loanInstallmentPayments.PaymentDate >= FromDate : true)
                    where (ToDate.HasValue ? loanInstallmentPayments.PaymentDate <= ToDate : true)
                    group loanInstallmentPayments by new { customersLoans.LoanID, customersLoans.LoanAmount } into p
                    select new
                    {
                        LoanID = p.Key.LoanID,
                        LoanAmount = p.Key.LoanAmount,
                        PaymentAmount = p.Select(c => c.PaymentAmount).Sum()
                    }
                      ).Select(c => (c.PaymentAmount >= c.LoanAmount) ? (double?)c.LoanAmount : (double?)c.PaymentAmount).Sum() ?? 0;
        }

        /// <summary>
        /// Gets the union status total due amount.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public double GetUnionStatusTotalDueAmount(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                    join loanExecutionInstallments in _Database.LoanExecutionInstallments on loanExecution.LoanExecutionID equals loanExecutionInstallments.LoanExecutionID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? loanExecutionInstallments.InstallmentDate >= FromDate : true)
                    where (ToDate.HasValue ? loanExecutionInstallments.InstallmentDate <= ToDate : true)
                    where loanExecutionInstallments.IsPaid == false
                    group loanExecutionInstallments by new { customersLoans.LoanID } into p
                    select new
                    {
                        LoanID = p.Key.LoanID,
                        PaymentAmount = p.Select(c => (c.TotalInstallmentAmount + c.PenaltyAmount)).Sum()
                    }
                      ).Select(c => (double?)c.PaymentAmount).Sum() ?? 0;
        }

        /// <summary>
        /// Gets the union status the funding from PAP.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public double GetUnionStatusTheFundingFromPAP(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from mainCommitteeDeposits in _Database.MainCommitteeDeposits
                    join committeeBankAccount in _Database.CommitteeBankAccounts on mainCommitteeDeposits.CommitteBankAccountID equals committeeBankAccount.CommitteeBankAccountID
                    join projects in _Database.Projects on committeeBankAccount.ProjectID equals projects.ProjectID
                    where (Projects.Count() != 0 ? Projects.Contains(projects.ProjectID) : true)
                    where (FromDate.HasValue ? mainCommitteeDeposits.DepositDate >= FromDate : true)
                    where (ToDate.HasValue ? mainCommitteeDeposits.DepositDate <= ToDate : true)
                    select (double?)mainCommitteeDeposits.Amount
                   ).Sum() ?? 0;
        }

        /// <summary>
        /// Gets the union status total fines collected.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public double GetUnionStatusTotalFinesCollected(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                    join loanExecutionInstallments in _Database.LoanExecutionInstallments on loanExecution.LoanExecutionID equals loanExecutionInstallments.LoanExecutionID
                    join loanInstallmentPayments in _Database.LoanInstallmentPayments on loanExecutionInstallments.LoanExecutionInstallmentID equals loanInstallmentPayments.LoanExecutionInstallmentID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? loanInstallmentPayments.PaymentDate >= FromDate : true)
                    where (ToDate.HasValue ? loanInstallmentPayments.PaymentDate <= ToDate : true)
                    select new
                    {
                        LoanID = customersLoans.LoanID,
                        FinesAmount = loanInstallmentPayments.PaymentAmount - loanExecutionInstallments.TotalInstallmentAmount
                    }
                    ).Sum(g => (double?)g.FinesAmount) ?? 0;

        }

        /// <summary>
        /// Gets the union status count of terminated payment loans.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfTerminatedPaymentLoans(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {

            var allDueInstallments = (from customersLoans in _Database.CustomerLoans
                                      join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                                      join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                                      join loanExecutionInstallments in _Database.LoanExecutionInstallments on loanExecution.LoanExecutionID equals loanExecutionInstallments.LoanExecutionID
                                      join loanInstallmentPayments in _Database.LoanInstallmentPayments on loanExecutionInstallments.LoanExecutionInstallmentID equals loanInstallmentPayments.LoanExecutionInstallmentID
                                      where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                                      where (FromDate.HasValue ? customersLoans.LoanCreatedDate >= FromDate : true)
                                      where (ToDate.HasValue ? customersLoans.LoanCreatedDate <= ToDate : true)
                                      group customersLoans by new { customersLoans.LoanID } into p
                                      select new
                                      {
                                          LoanID = p.Key.LoanID,
                                          CountOfInstallments = p.Count()
                                      }
                                     );

            var allPaidInstallments = (from customersLoans in _Database.CustomerLoans
                                       join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                                       join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                                       join loanExecutionInstallments in _Database.LoanExecutionInstallments on loanExecution.LoanExecutionID equals loanExecutionInstallments.LoanExecutionID
                                       join loanInstallmentPayments in _Database.LoanInstallmentPayments on loanExecutionInstallments.LoanExecutionInstallmentID equals loanInstallmentPayments.LoanExecutionInstallmentID
                                       where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                                       where (FromDate.HasValue ? customersLoans.LoanCreatedDate >= FromDate : true)
                                       where (ToDate.HasValue ? customersLoans.LoanCreatedDate <= ToDate : true)
                                       where (loanExecutionInstallments.IsPaid == true)
                                       group customersLoans by new { customersLoans.LoanID } into p
                                       select new
                                       {
                                           LoanID = p.Key.LoanID,
                                           CountOfInstallments = p.Count()
                                       }
                                    );


            return (from allDueInstal in allDueInstallments
                    join allPaidInstal in allPaidInstallments
                    on new { allDueInstal.LoanID, allDueInstal.CountOfInstallments } equals new { allPaidInstal.LoanID, allPaidInstal.CountOfInstallments }
                    select allDueInstal.LoanID).Count();

        }

        /// <summary>
        /// Gets the union status the benefits repayed.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public double GetUnionStatusTheBenefitsRepayed(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                    join loanExecutionInstallments in _Database.LoanExecutionInstallments on loanExecution.LoanExecutionID equals loanExecutionInstallments.LoanExecutionID
                    join loanInstallmentPayments in _Database.LoanInstallmentPayments on loanExecutionInstallments.LoanExecutionInstallmentID equals loanInstallmentPayments.LoanExecutionInstallmentID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? loanInstallmentPayments.PaymentDate >= FromDate : true)
                    where (ToDate.HasValue ? loanInstallmentPayments.PaymentDate <= ToDate : true)
                    where (loanInstallmentPayments.PaymentAmount >= loanExecutionInstallments.TotalInstallmentAmount)
                    select (double?)loanExecutionInstallments.InterestInstallmentAmount
                   ).Sum() ?? 0;
        }

        /// <summary>
        /// Gets the union status repayment percentage.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public double GetUnionStatusRepaymentPercentage(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            var paymentData = (from customersLoans in _Database.CustomerLoans
                               join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                               join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                               join loanExecutionInstallments in _Database.LoanExecutionInstallments on loanExecution.LoanExecutionID equals loanExecutionInstallments.LoanExecutionID
                               join loanInstallmentPayments in _Database.LoanInstallmentPayments on loanExecutionInstallments.LoanExecutionInstallmentID equals loanInstallmentPayments.LoanExecutionInstallmentID
                               where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                               where (FromDate.HasValue ? loanInstallmentPayments.PaymentDate >= FromDate : true)
                               where (ToDate.HasValue ? loanInstallmentPayments.PaymentDate <= ToDate : true)
                               select new
                               {
                                   LoanID = customersLoans.LoanID,
                                   PaymentAmount = loanInstallmentPayments.PaymentAmount,
                                   TotalInstallmentAmount = loanExecutionInstallments.TotalInstallmentAmount
                               }
                    );

            double totalPaymentAmount = paymentData.Select(p => (double?)p.PaymentAmount).Sum() ?? 0;
            double totalInstallmentAmount = paymentData.Select(p => (double?)p.TotalInstallmentAmount).Sum() ?? 0;

            if (totalInstallmentAmount == 0)
                return 0;

            return (totalPaymentAmount / totalInstallmentAmount) * 100;
        }

        /// <summary>
        /// Gets the union status count of loans transfered to court.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfLoansTransferedToCourt(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanCases in _Database.LoanCases on customersLoans.LoanID equals loanCases.LoanID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? loanCases.CaseDate >= FromDate : true)
                    where (ToDate.HasValue ? loanCases.CaseDate <= ToDate : true)
                    select customersLoans.LoanID
                   ).Count();
        }

        /// <summary>
        /// Gets the union status count of primary loans.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfPrimaryLoans(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? loanExecution.ExecutionDate >= FromDate : true)
                    where (ToDate.HasValue ? loanExecution.ExecutionDate <= ToDate : true)
                    where (projectstage.Stage == 1)
                    select customersLoans.LoanID
                    ).Count();
        }

        /// <summary>
        /// Gets the union status count of secondary loans.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfSecondaryLoans(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? loanExecution.ExecutionDate >= FromDate : true)
                    where (ToDate.HasValue ? loanExecution.ExecutionDate <= ToDate : true)
                    where (projectstage.Stage == 2)
                    select customersLoans.LoanID
                    ).Count();
        }

        /// <summary>
        /// Gets the union status count of loans that exceed the three stage.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfLoansThatExceedTheThreeStage(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? loanExecution.ExecutionDate >= FromDate : true)
                    where (ToDate.HasValue ? loanExecution.ExecutionDate <= ToDate : true)
                    where (projectstage.Stage != 1 && projectstage.Stage != 2)
                    select customersLoans.LoanID
                    ).Count();
        }

        /// <summary>
        /// Gets the union status count of renewal loans.
        /// </summary>
        /// <param name="Projects">The projects.</param>
        /// <param name="FromDate">From date.</param>
        /// <param name="ToDate">To date.</param>
        /// <returns></returns>
        public int GetUnionStatusCountOfRenewalLoans(List<Guid> Projects, DateTime? FromDate, DateTime? ToDate)
        {
            return (from customersLoans in _Database.CustomerLoans
                    join projectstage in _Database.ProjectStages on customersLoans.ProjectStageID equals projectstage.ProjectStageID
                    join loanExecution in _Database.LoanExecutions on customersLoans.LoanID equals loanExecution.LoanID
                    where (Projects.Count() != 0 ? Projects.Contains(projectstage.ProjectID) : true)
                    where (FromDate.HasValue ? loanExecution.ExecutionDate >= FromDate : true)
                    where (ToDate.HasValue ? loanExecution.ExecutionDate <= ToDate : true)
                    where (projectstage.Stage != 1)
                    select customersLoans.LoanID
                    ).Count();
        }
        #endregion

        #endregion


        /// <summary>
        /// Finds the military statuses.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ILookUpEntity> FindMilitaryStatuses(System.Linq.Expressions.Expression<Func<ILookUpEntity, bool>> where)
        {
            return _Database.MilitaryStatuses.OfType<ILookUpEntity>();
        }


        /// <summary>
        /// Finds the loan legal forms.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ILookUpEntity> FindLoanLegalForms(System.Linq.Expressions.Expression<Func<ILookUpEntity, bool>> where)
        {
            return _Database.LoanLegalForms.OfType<ILookUpEntity>();
        }


        /// <summary>
        /// Finds the loan steps.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IwfStep> FindLoanSteps(System.Linq.Expressions.Expression<Func<IwfStep, bool>> where)
        {
            return _WorkFlow.wfSteps.Where(where);
        }


        /// <summary>
        /// Finds the total system report.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IVTotalSystemReport> FindTotalSystemReport(System.Linq.Expressions.Expression<Func<IVTotalSystemReport, bool>> where)
        {
            return _Database.VTotalSystemReport.Where(where);
        }
    }
}
